<?php
include('../../include/functions.inc.php');
if (!isEmpty(getParam('toexcel'))) {
    header("Content-type: application/vnd.ms-excel; name='excel'");
    header("Content-Disposition: filename=Facturas.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
}

$arrMeses = array();
$arrMeses[1]['mes'] = 'Ene';
$arrMeses[1]['mest'] = 'Enero';
$arrMeses[2]['mes'] = 'Feb';
$arrMeses[2]['mest'] = 'Febrero';
$arrMeses[3]['mes'] = 'Mar';
$arrMeses[3]['mest'] = 'Marzo';
$arrMeses[4]['mes'] = 'Abr';
$arrMeses[4]['mest'] = 'Abril';
$arrMeses[5]['mes'] = 'May';
$arrMeses[5]['mest'] = 'Mayo';
$arrMeses[6]['mes'] = 'Jun';
$arrMeses[6]['mest'] = 'Junio';
$arrMeses[7]['mes'] = 'Jul';
$arrMeses[7]['mest'] = 'Julio';
$arrMeses[8]['mes'] = 'Ago';
$arrMeses[8]['mest'] = 'Agosto';
$arrMeses[9]['mes'] = 'Sep';
$arrMeses[9]['mest'] = 'Septiembre';
$arrMeses[10]['mes'] = 'Oct';
$arrMeses[10]['mest'] = 'Octubre';
$arrMeses[11]['mes'] = 'Nov';
$arrMeses[11]['mest'] = 'Noviembre';
$arrMeses[12]['mes'] = 'Dic';
$arrMeses[12]['mest'] = 'Diciembre';

//$meses = array("ene", "feb", "mar", "abr", "may", "jun", "jul", "ago", "sep", "oct", "nov", "dic", "nom", "car", "emp", "nit");
$hide = array();
if (isset($_SESSION["hide"])) {
    $hide = unserialize($_SESSION["hide"]);
}
if (!isEmpty(getParam("show"))) {
    if (getParam("show") == "all") {
        unset($_SESSION["hide"]);
        $hide = array();
    } else {
        unset($hide[array_search(getParam("show"), $hide)]);
    }
}

$anioActual = getParam("anio2");
if( isEmpty($anioActual) ) {
    $anioActual = date("Y");
}
//$anioActual = date("Y");
$arrAnio = array();
$arrAnio[$anioActual] = $anioActual;
$arrAnio[$anioActual+1] = $anioActual+1;
$arrAnio[$anioActual+2] = $anioActual+2;

//validacion de ocultacion de meses
/*foreach ($meses as $m) {
    if (!isEmpty(getParam($m))) {
        array_push($hide, $m);
    }
}*/
reset($arrAnio); 
while( $arrA = each($arrAnio) ) {
    reset($arrMeses);
    while( $arrM = each($arrMeses) ) {
        if (!isEmpty(getParam($arrM['value']['mes'].'_'.$arrA['key']))) {
            array_push($hide, $arrM['value']['mes'].'_'.$arrA['key']);
        }
    }    
}

//carrera activas
$where = "";
if (!isEmpty(getParam("CR")) && getParam("CR") >= 0) {
    $where = "id=" . sqlValue(getParam("CR"), "int");
    $carreras = $db->query_select("cllg_career", $where);
} else if (!isEmpty(getParam("CR")) && getParam("CR") < 0) {
    $carreras[] = array("ID" => -1, "NAME" => "ALUMNOS NO INSCRITOS EN ESTE CICLO");
} else {
    $carreras = $db->query_select("cllg_career", null);
    $carreras[] = array("ID" => -1, "NAME" => "ALUMNOS NO INSCRITOS EN ESTE CICLO");
}


//ciclos del a;o seleccionado
//$queryCiclos="select CODE from cllg_college_cycle cc
//   where cc.DATE_BEGIN>='$anioActual/01/01' and cc.DATE_END<='$anioActual/31/01'";
$queryCiclos = "select CODE,DATE_BEGIN,DATE_END from cllg_college_cycle c where c.STATUS=1";
if (!isEmpty(getParam('anio'))) {
    $paraCiclo = getParam('anio');
    $queryCiclos = "select CODE,DATE_BEGIN,DATE_END from cllg_college_cycle c where c.CODE=$paraCiclo";
}
$cCiclo = $db->queryToArray($queryCiclos);
$cCiclo = $cCiclo[0];

function getFacturas($db, $id, $anio) {
    $qryFacts = "
       select * from (SELECT
          ID,NUMERO,
          SERIE,FK_STUDENT,
          EXTRACT(YEAR FROM cllg_factura.FECHA) ANIO,
          EXTRACT(MONTH FROM cllg_factura.FECHA) MES
        FROM
          cllg_factura
        WHERE cllg_factura.FK_STUDENT=$id AND EXTRACT(YEAR FROM cllg_factura.FECHA)=$anio AND cllg_factura.STATUS=1
        UNION
        SELECT
          ID,NUMERO,
          SERIE,FK_STUDENT,
          EXTRACT(YEAR FROM cllg_factura.FECHA) ANIO,
          EXTRACT(MONTH FROM cllg_factura.FECHA) MES
        FROM
          cllg_factura
        WHERE 
            EXTRACT(YEAR FROM cllg_factura.FECHA)=$anio AND cllg_factura.STATUS=1 and        
            cllg_factura.ID IN 
                (SELECT DISTINCT FK_FACTURA
                        FROM cllg_mov_cargo
                  WHERE 
                  FK_CARGO IN( SELECT ID FROM cllg_cargo
                                    WHERE FK_ACCOUNT IN(  SELECT ID FROM cllg_account WHERE FK_STUDENT=$id)))) a  ORDER BY ANIO,MES";
    $facturas = $db->queryToArray($qryFacts);
    return $facturas;
}

function getFacturasInscripcion($db, $id, $anio) {
    $qryFacts ="SELECT * from ( SELECT ID,
                        NUMERO,
                        SERIE,
                        FK_STUDENT,
                        EXTRACT(YEAR FROM cllg_factura.FECHA) ANIO,
                        EXTRACT(MONTH FROM cllg_factura.FECHA) MES
                FROM    cllg_factura
                WHERE   cllg_factura.FK_STUDENT = $id
                AND     EXTRACT(YEAR FROM cllg_factura.FECHA)= $anio 
                AND     cllg_factura.STATUS=1 
                AND     cllg_factura.ID IN( SELECT  d.FK_FACTURA ID
                                            FROM    cllg_factura_detalle d
                                            WHERE   d.DESCRIPCION like '%Inscrip%' group by ID)
                UNION
                SELECT  ID,
                        NUMERO,
                        SERIE,
                        '$id' as FK_STUDENT,
                        EXTRACT(YEAR FROM cllg_factura.FECHA) ANIO,
                        EXTRACT(MONTH FROM cllg_factura.FECHA) MES
                FROM    cllg_factura
                WHERE   cllg_factura.FK_STUDENT = $id
                AND     EXTRACT(YEAR FROM cllg_factura.FECHA)= $anio 
                AND     cllg_factura.STATUS=1 
                AND     cllg_factura.ID IN( SELECT  d.FK_FACTURA ID
                                            FROM    cllg_factura_detalle d
                                            WHERE   d.DESCRIPCION like '%Inscrip%' group by ID)
                AND     cllg_factura.ID IN 
                (SELECT DISTINCT FK_FACTURA
                        FROM cllg_mov_cargo
                  WHERE 
                  FK_CARGO IN( SELECT ID FROM cllg_cargo
                                    WHERE FK_ACCOUNT IN(  SELECT ID FROM cllg_account WHERE FK_STUDENT=$id)))) b                          
                ORDER BY ANIO,MES";
    $facturas = $db->queryToArray($qryFacts);
    $strFacts = "";
    $c = 0;
    foreach ($facturas as $f) {
        $strFacts.='<a href="javascript:;" onclick="viewFactura(' . $f["ID"] . ')">' . $f["SERIE"] . "-" . $f["NUMERO"] . '</a>';
        $c++;
        $strFacts.=($c == 2) ? "<br>" : "&nbsp;";
        $c = ($c == 2) ? 0 : $c;
    }
    return $strFacts;
}

function getMontoInscripcion($db, $id, $anio, $formated = true) {
    $sinStotal = 0;
    $qryFacts = "SELECT sum(d.SUBTOTAL) STOTAL FROM  cllg_factura_detalle d
                WHERE
                  d.DESCRIPCION like '%Inscrip%' AND
                  d.FK_FACTURA
                in(SELECT ID FROM cllg_factura f where EXTRACT(YEAR FROM f.FECHA)=$anio AND f.STATUS=1 AND f.FK_STUDENT=$id)";
    $resultado = $db->queryToArray($qryFacts);
    if( $formated ) {
        $sinStotal = isset($resultado[0]["STOTAL"]) ? number_format(intval($resultado[0]["STOTAL"]), 2) : 0;
        return "<span style='color:blue;'>" . $sinStotal . "</span>";
    }
    else {
        $sinStotal = isset($resultado[0]["STOTAL"]) ? $resultado[0]["STOTAL"] : 0;
        return $sinStotal;
    }
}

function getSumAbonos($db, $id, $anio, $formated = false) {
    //suma de abonos agrupado por estudiante,mes y a;o
    $abonos = $db->queryToArray("SELECT
       FK_STUDENT STUD, SUM(TOTAL) stotal,
       EXTRACT(YEAR FROM FECHA) ANIO
    FROM cllg_factura
    WHERE
        FK_STUDENT =$id AND
        EXTRACT(YEAR FROM FECHA)=$anio AND
        STATUS=1
    group by STUD,ANIO
    ORDER BY STUD,ANIO");

    $total = isset($abonos[0]["stotal"]) ? $abonos[0]["stotal"] : 0;

    if ($formated)
        return number_format($total, 2);
    else
        return $total;
}

function getAbonos($db, $id, $anio) {
    //suma de abonos agrupado por estudiante,mes y a;o
    $abonos = $db->queryToArray("SELECT
       FK_STUDENT STUD, SUM(TOTAL) stotal,
       EXTRACT(MONTH FROM FECHA) MES,
       EXTRACT(YEAR FROM FECHA) ANIO
    FROM cllg_factura
    WHERE
        FK_STUDENT =$id AND
        EXTRACT(YEAR FROM FECHA)=$anio AND
        STATUS=1
    group by STUD,ANIO,MES
    ORDER BY STUD,MES,ANIO");
    return $abonos;
}

function getSumAbonosByMonth($db, $studs, $anio, $month, $value = false) {
    //suma de abonos agrupado por estudiante,mes y a;o
    $qryAbonos = "SELECT
       SUM(TOTAL) stotal,
       EXTRACT(YEAR FROM FECHA) ANIO
    FROM cllg_factura
    WHERE
        FK_STUDENT IN($studs) AND
        EXTRACT(YEAR FROM FECHA)=$anio AND
        EXTRACT(MONTH FROM FECHA) = $month AND
        STATUS=1
    group by ANIO
    ORDER BY ANIO";
    $abonos = $db->queryToArray($qryAbonos);
    //drawDebug($abonos);
    //echo $qryAbonos;
    if( !$value ) {
        $sinAbonos = isset($abonos[0]["stotal"]) ? number_format($abonos[0]["stotal"], 2) : 0;
        return '<strong> <span style="color:blue;" >' . $sinAbonos . '</strong></span>';
    }                                                                                         
    else {
        return isset($abonos[0]["stotal"]) ? $abonos[0]["stotal"] : 0;
    }
        
}

function getSumDeuda($db, $id, $anio, $formated = false) {
    $pendientesSinPago="SELECT  STUD, sum(stotal) TOTAL FROM(
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.AMOUNT) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT 
                        AND     cc.FK_STUDENT=$id
                        AND     ISNULL(cllg_cargo.DATE_PAYED)
                        AND     ISNULL(cllg_cargo.TOTAL_PENDIENTE)
                        GROUP   BY STUD
                        UNION
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.TOTAL_PENDIENTE) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT
                        AND     cc.FK_STUDENT = $id 
                        AND     cllg_cargo.TOTAL_PENDIENTE > 0
                        GROUP   BY STUD
                        UNION
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.PENALTY) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT 
                        AND     cc.FK_STUDENT=$id
                        AND     ISNULL(cllg_cargo.DATE_PAYED)
                        AND     cllg_cargo.DATE_LIMIT < NOW()
                        AND     ISNULL(cllg_cargo.TOTAL_PENDIENTE)
                        GROUP   BY STUD
                        ORDER   BY  STUD) DEUDAS GROUP BY STUD";
    $deuda = $db->queryToArray($pendientesSinPago);
    if( $formated )
        return isset($deuda[0]["TOTAL"]) ? number_format($deuda[0]["TOTAL"], 2) : 0;
    else
        return isset($deuda[0]["TOTAL"]) ? $deuda[0]["TOTAL"] : 0;
}

function getFacturasMes($mes, $facturas) {
    $strFacts = "";
    $c = 0;
    foreach ($facturas as $f) {
        if ($f["MES"] == $mes) {
            $strFacts.='<a href="javascript:;" onclick="viewFactura(' . $f["ID"] . ')">' . $f["SERIE"] . "-" . $f["NUMERO"] . '</a>';
            $c++;
            $strFacts.=($c == 2) ? "<br>" : "&nbsp;";
            $c = ($c == 2) ? 0 : $c;
        }
    }
    return $strFacts;
}

function getAbonosMes($mes, $abonos) {
    $totalAbono = 0;
    foreach ($abonos as $abono) {
        if ($abono["MES"] == $mes) {
            $totalAbono+=$abono["stotal"];
        }
    }
    $color = "blue";
    if ($totalAbono == 0)
        $color = "red";

    return "<span style='color:$color;'>" . number_format($totalAbono, 2) . '</span>';
}

function getCargosPMes($mes, $cargosP) {
    $totalCargos = 0;
    foreach ($cargosP as $cargo) {
        if ($cargo["MES"] == $mes) {
            $totalCargos+=$cargo["stotal"];
        }
    }
    return $totalCargos > 0 ? ' ,<span style="color:red;"> Pend. ' . number_format($totalCargos, 2) . '</span>' : '&nbsp';
}

if( isEmpty(getParam("ajax")) ) {
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html>
        <head>
            <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
            <title>Reporte Estado de Cuotas: <?= $anioActual ?></title>
            <style type="text/css">
                <!--
                #general {
                    height: 100%;
                }
                body {
                    font-family: Arial, Helvetica, sans-serif;
                }
                -->
            </style>
            <link href="../../css/tablereportes.css" rel="stylesheet" type="text/css" />
            <link href="../../js/dialog/dialog.2.0.css" rel="stylesheet" type="text/css" />
            <script src="../../js/prototype162.js"></script>
            <script src="../../js/dialog/dialog.2.0.js" type="text/javascript"></script>
            <script src="../../js/include.js"></script>
            <script type="text/javascript">
                function mostrar(mes){
                    waitMsg("Cargando...")
                    var p=$(document.forms[0]).serialize()+"&ajax=true&show="+mes;
                    new Ajax.Updater("general","estadoCuotasReporte.php",
                    { parameters:p,onComplete:function(){ closeWait(); }});
                }
                function hideMes(ob){
                    if(!ob.checked){
                        waitMsg("Cargando...")
                        var p=$(document.forms[0]).serialize()+"&ajax=true&"+ob.name+"="+ob.value;
                        new Ajax.Updater("general","estadoCuotasReporte.php",
                        { parameters:p, onComplete:function(){ closeWait(); }});
                    }
                }
                viewFactura=function(id){
                    URL="../facturasbrowser/view.php?fromReport=true&id="+id;
                    reporte = window.open(URL,'_blank', 'toolbar=0,scrollbars=1,location=0,statusbar=0,menubar=0,resizable=0,width=550,height=500,left = 0,top = 0');
                }
            </script>
        </head>
        <body>
            <H1>Reporte Estado de Cuotas, A&ntilde;o : <?= $anioActual ?></H1>
            <form name="filtro" id="filtro" action="estadoCuotasReporte.php" method="post" target="_self">
                <?php
                $combo = $db->query_select("cllg_career", "FK_ENTITY=" . $user->entity);
                $combo[] = array("ID" => -1, "NAME" => "ALUMNOS NO INSCRITOS EN ESTE CICLO");
                ?>
                <h3>
                    Carrera:
                    <select name="CR" id="CR" onchange="this.form.submit();">
                        <option value="">Todas</option>
                        <?php
                        foreach ($combo as $it) {
                            ?>
                            <option <?php if (!(strcmp($it["ID"], getParam('CR')))) {
                                echo "selected=\"selected\"";
                            } ?> value="<?php echo $it["ID"]; ?>"><?php echo $it["NAME"]; ?> </option>
                            <?php
                        }
                        ?>
                    </select>
                </h3>
                <h3>
                    A&ntilde;o:
                    <select name="anio2" id="anio2" onchange="this.form.submit();">
                        <?php
                        $strQuery = 'SELECT DISTINCT YEAR(date_begin) anio from cllg_college_cycle ORDER BY date_begin';
                        $db->query($strQuery);
                        while( $r = $db->fetch_assoc() ) {
                            ?>
                            <option <?php if( getParam('anio2') == $r["anio"] ) {
                                echo "selected=\"selected\"";
                            }
                            ?>  value="<?= $r["anio"] ?>"><?= $r["anio"] ?></option>
                            <?php    
                        }
                        $db->free_result($r);
                        $ciclos = $db->query_select("cllg_college_cycle", null);
                        ?>
                    </select>
                </h3>
                <h3>
                    Ciclo:
                    <?php
                    $ciclos = $db->query_select("cllg_college_cycle", null);
                    ?>
                    <select name="anio" id="anio" onchange="this.form.submit();">
                        <?php
                        foreach ($ciclos as $ciclo) {
                            ?>
                            <option <?php if (getParam('anio') == $ciclo["CODE"] || (isEmpty(getParam('anio')) && $ciclo["STATUS"] == 1)) {
                                echo "selected=\"selected\"";
                            }
                            ?>  value="<?= $ciclo["CODE"] ?>"><?= $ciclo["CODE"] ?></option>
                            <?php
                        }
                        ?>
                    </select>
                </h3>
                <?php
                if( isEmpty(getParam('toexcel')) ) {
                    ?>
                    <h2 id="controles">
                        <input type="hidden" name="toexcel" id="toexcel" value="">
                        <button type="submit" value="" onclick="this.form.target='_blank'; return true;">Abrir Aparte</button>&nbsp;
                        <button type="button" onclick="$('controles').hide();window.print();alert('Impresion Lista!');$('controles').show();" >Imprimir</button>
                        <button type="submit" onclick="$('toexcel').value='yes';" >Excel</button>
                    </h2>
                    <?php
                    }
                    ?>
                </form>
                <div id="general">
                <?php
}
else {
    header("Content-type: text/html; charset=iso-8859-1");
}

$meseOcultos = "<h4>Mostrar Ocultos: ";
foreach ($hide as $m) {
    $p = "'$m'";
    $meseOcultos.='<a href="javascript:;" onclick="mostrar(' . $p . ');">' . $m . '</a>&nbsp;';
}
$meseOcultos.='<a href="javascript:;" onclick="mostrar(\'' . "all" . '\');">Todos</a>&nbsp;';
$meseOcultos.="</h4>";
echo $meseOcultos;

$arrTrimestre = array();
foreach ($carreras as $cr) {
    $tr1 = 0;
    $tr2 = 0;
    $tr3 = 0;
    $tr4 = 0;
    ?>
    <table width="200%" id="tablareporte" border="0" cellspacing="0" cellpadding="2">
        <caption>[ <?= $cr["ID"] ?> ]&nbsp;<?= $cr["NAME"] ?></caption>
        <thead>
            <tr>
                <th rowspan="2" scope="col">No.</th>
                <?php
                if( isEmpty(getParam("car")) && !in_array("car", $hide) ) {
                    ?>
                    <th rowspan="2" scope="col">
                        <input  name="car" type="checkbox" id="car" onclick="hideMes(this)" value="1" checked="checked"  /><br />
                        Carnet
                    </th>
                    <?php
                }
                if( isEmpty(getParam("nom")) && !in_array("nom", $hide) ) {
                    ?>
                    <th rowspan="2" scope="col">
                        <input  name="nom" type="checkbox" id="nom" onclick="hideMes(this)" value="1" checked="checked"  /><br />
                        Nombre
                    </th>
                    <?php
                }
                if( isEmpty(getParam("emp")) && !in_array("emp", $hide) ) {
                    ?>
                    <th rowspan="2" scope="col">
                        <input  name="emp" type="checkbox" id="emp" onclick="hideMes(this)" value="1" checked="checked"  /><br />
                        Empresa
                    </th>
                    <?php
                }
                if( isEmpty(getParam("nit")) && !in_array("nit", $hide) ) {
                    ?>
                    <th rowspan="2" scope="col">
                        <input  name="nit" type="checkbox" id="nit" onclick="hideMes(this)" value="1" checked="checked"  /><br />
                        Nit
                    </th>
                    <?php
                }
                ?>
                <!--<th colspan="2" scope="col">Incripciones</th>-->
                <?php
                reset($arrAnio);
                while( $arrA = each($arrAnio) ) {
                    $arrTrimestre[$arrA['key']][1] = 0;
                    $arrTrimestre[$arrA['key']][2] = 0;
                    $arrTrimestre[$arrA['key']][3] = 0;
                    $arrTrimestre[$arrA['key']][4] = 0;
                    ?>
                    <th colspan="2" scope="col">Inscripciones</th>
                    <?php
                    reset($arrMeses);
                    while( $arrM = each($arrMeses) ) {
                        if( isEmpty(getParam($arrM['value']['mes'].'_'.$arrA['key'])) && !in_array($arrM['value']['mes'].'_'.$arrA['key'], $hide) ) {
                            ?>
                            <th colspan="2" scope="col">
                                <input name="<?php print $arrM['value']['mes']; ?>_<?php print $arrA['key']; ?>" type="checkbox" id="<?php print $arrM['value']['mes']; ?>_<?php print $arrA['key']; ?>" onclick="hideMes(this)" value="1" checked="checked" /><br />
                                <label for="<?php print $arrM['value']['mes']; ?>"><?php print $arrM['value']['mes']; ?>&nbsp;<?php print $arrA['value']; ?></label>
                            </th>
                            <?php 
                        }
                        else {
                            if( $arrM['key'] <= 3 ) {
                                $arrTrimestre[$arrA['key']][1]++;    
                            }
                            else if($arrM['key'] <= 6 ) {
                                $arrTrimestre[$arrA['key']][2]++;    
                            }
                            else if($arrM['key'] <= 9 ) {
                                $arrTrimestre[$arrA['key']][3]++;    
                            }
                            else if($arrM['key'] <= 12 ) {
                                $arrTrimestre[$arrA['key']][4]++;    
                            }
                            $tr1++;
                        }    
                    }
                }
                ?>
                <th align="right" rowspan="2" scope="col">Total Pagado</th>
                <th align="right" rowspan="2" scope="col">Total Pendiente</th>
            </tr>
            <tr>
                <!--<th>Facturas</th>
                <th>Total</th>-->
                <?php
                reset($arrAnio);
                while( $arrA = each($arrAnio) ) {
                    ?>
                    <th>Facturas</th>
                    <th>Total</th>
                    <?php
                    reset($arrMeses);
                    while( $arrM = each($arrMeses) ) {
                        if( isEmpty(getParam($arrM['value']['mes'].'_'.$arrA['key'])) && !in_array($arrM['value']['mes'].'_'.$arrA['key'], $hide) ) {
                            ?>
                            <th>Facturas</th>
                            <th>Cuota</th>
                            <?php 
                        }    
                    }
                }
                ?>
            </tr>
        </thead>
        <tbody>
            <?php
            $codCiclo = $cCiclo["CODE"];
            $careerId = $cr["ID"];
            if( $careerId >= 0 ) {
                //estudiantes activos en ciclo seleccionado
                $carnets = "select FK_STUDENT from cllg_student_assign b  where b.FK_CYCLE IN ($codCiclo) AND b.FK_CAREER=$careerId";
                $inscritos = "select ID CARNET ,LAST_NAME,FIRST_NAME,SECOND_NAME,BIRTHDATE,FK_EMPRESA  from cllg_student   where ID IN ( $carnets ) ORDER BY LAST_NAME,FIRST_NAME";
                $listadoAlumnos = $inscritos;
            }
            else {
                //estudiantes no inscritos con operaciones en el ciclo seleccionado
                $fechaInicioCiclo = sqlValue($cCiclo["DATE_BEGIN"], "date");
                $fechaFinCiclo = sqlValue($cCiclo["DATE_END"], "date");
                $carnets = "select cc.FK_STUDENT from cllg_cargo c,cllg_account cc
                                                        where NOT ISNULL(c.DATE_PAYED) AND
                                                          c.DATE_LIMIT BETWEEN $fechaInicioCiclo AND $fechaFinCiclo AND
                                                          c.FK_ACCOUNT = cc.ID AND
                                                          cc.FK_STUDENT NOT IN (select FK_STUDENT
                                                                from cllg_student_assign b
                                                                where b.FK_CYCLE IN ($codCiclo))
                                                        GROUP BY cc.FK_STUDENT";
                $lsCarnets = $db->queryToArray($carnets); //se hace la consulta para evitar recargar el servidor
                $carnets = "";
                foreach ($lsCarnets as $car) {
                    $carnets.=!isEmpty($carnets) ? "," : "";
                    $carnets.=$car["FK_STUDENT"];
                }
                $carnets = empty($carnets) ? 0 : $carnets;
                $noInscritos = "SELECT  ID CARNET,LAST_NAME,FIRST_NAME,SECOND_NAME,BIRTHDATE,FK_EMPRESA
                                FROM    cllg_student
                                where   ID IN($carnets)";
                $listadoAlumnos = $noInscritos;
            }
            $estudiantes = array();
            $estudiantes = $db->queryToArray($listadoAlumnos);

            $abonos = array();
            $facturas = array();
            $cargosPend = array();
            $no = 1;
            $deudaAnual = 0;
            $abonoAnual = 0;
            
            if (count($estudiantes) == 0){
                ?>
                <tr>
                    <td style="color:blue;font-weight: bolder" colspan="24">
                        NO HAY ALUMNOS ASIGNADOS EN ESTE CICLO Y CARRERA
                    </td>
                </tr>
                <?php
                }else{
                    
                foreach( $estudiantes as $stu ) {
                    $sqlStat = "select * from cllg_student_assign b  where b.FK_CYCLE IN ($codCiclo) AND b.FK_CAREER=$careerId AND STATUS=1 AND FK_STUDENT={$stu['CARNET']}";
                    $stat = $db->queryToArray($sqlStat);
                    #$empresa = getFieldValue($db, "cllg_student", $stu["CARNET"], "COMPANY_NAME");
                    $empresa = $stu['FK_EMPRESA'] != NULL ? $db->query_select("cllg_empresa",sprintf("ID=%s",$stu['FK_EMPRESA'])) : "&nbsp;";
                    $nit = getFieldValue($db, "cllg_student", $stu["CARNET"], "NIT");
                    ?>
                    <tr>
                        <td><?= $no ?></td>
                        <?php
                        if( isEmpty(getParam("car")) && !in_array("car", $hide) ) {
                            ?>
                            <td><?= $stu["CARNET"] ?></td>
                            <?php
                        }
                        if( isEmpty(getParam("nom")) && !in_array("nom", $hide) ) {
                            ?>
                            <td nowrap style="<?php echo (count($stat) > 0 ? "" : "text-decoration:line-through"); ?>"><?= $stu["FIRST_NAME"] . " " . $stu["SECOND_NAME"] . " " . $stu["LAST_NAME"] ?></td>
                            <?php
                        }
                        if( isEmpty(getParam("emp")) && !in_array("emp", $hide)) {
                            ?>
                            <td><? echo($stu['FK_EMPRESA'] != NULL ? $empresa[0]['NOMBRE'] : $empresa); ?></td>
                            <?php
                        }
                        if( isEmpty(getParam("nit")) && !in_array("nit", $hide)) {
                            ?>
                            <td><?= $nit[0]["VALUE"] ?></td>
                            <?php
                        }
                        $sinSumAbonos = 0;
                        $sinSumDeuda = 0;
                        reset($arrAnio);
                        while( $arrA = each($arrAnio) ) {
                            $abonos = getAbonos($db, $stu["CARNET"], $arrA['key']);
                            $facturas = getFacturas($db, $stu["CARNET"], $arrA['key']);
                            $abonoAnual+=getSumAbonos($db, $stu["CARNET"], $arrA['key']);
                            //$deudaAnual+=getSumDeuda($db, $stu["CARNET"], $arrA['key']);
                            ?>
                            <td nowrap><?= getFacturasInscripcion($db, $stu["CARNET"], $arrA['key']) ?></td>
                            <td><?= getMontoInscripcion($db, $stu["CARNET"], $arrA['key']) ?></td>
                            <?php
                            reset($arrMeses);
                            while( $arrM = each($arrMeses) ) {
                                if( isEmpty(getParam($arrM['value']['mes'].'_'.$arrA['key'])) && !in_array($arrM['value']['mes'].'_'.$arrA['key'], $hide) ) {
                                    ?>
                                    <td nowrap><?= getFacturasMes($arrM['key'], $facturas) ?></td>
                                    <td><?= getAbonosMes($arrM['key'], $abonos) ?></td>
                                    <?php
                                }    
                            }
                            $sinSumAbonos += getSumAbonos($db, $stu["CARNET"], $arrA['key'], false); 
                            $sinSumDeuda = getSumDeuda($db, $stu["CARNET"], $arrA['key'], false); 

                        }
                        $deudaAnual+=getSumDeuda($db, $stu["CARNET"], $arrA['key']);
                        ?>
                        <td align="right"><strong><span style="color:blue;"><?php print number_format($sinSumAbonos, 2); ?></span></strong></td>
                        <td align="right"><strong><span id="MUESTRA" style="color:red;"><?php print number_format($sinSumDeuda, 2); ?></span></strong></td>
                    </tr>
                    <?php
                    $no++;
                    }
                }
            ?>
            <tr>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
                <td colspan="3">Total por Mes</td>
                <?php
                reset($arrAnio);
                while( $arrA = each($arrAnio) ) {
                    ?>                
                    <td>Inscripciones</td>
                    <td>&nbsp;</td>
                    <?php
                    reset($arrMeses);
                    while( $arrM = each($arrMeses) ) {
                        if( isEmpty(getParam($arrM['value']['mes'].'_'.$arrA['key'])) && !in_array($arrM['value']['mes'].'_'.$arrA['key'], $hide) ) {
                            ?>
                            <td><?php print $arrM['value']['mest']; ?>&nbsp;<?php print $arrA['key']; ?></td>
                            <td><?= getSumAbonosByMonth($db, $carnets, $arrA['key'], $arrM['key']) ?></td>
                            <?php
                        }    
                    }
                }
                ?>
                <td align="right"><strong><span style="color:blue;"><?= number_format($abonoAnual, 2) ?></span></strong></td>
                <td align="right"><strong><span style="color:red;"><?= number_format($deudaAnual, 2) ?></span></strong></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td>&nbsp;</td>
                <td colspan="3">Total por Trimestre</td>
                <!--<td>&nbsp;</td>
                <td>&nbsp;</td>-->
                <?php
                reset($arrAnio);
                while( $arrA = each($arrAnio) ) {
                    ?>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <?php
                    if( $arrTrimestre[$arrA['key']][1] < 3 ) {
                        ?>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][1]) ?>" align="right">Trimestre 1</td>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][1]) ?>" align="left">
                            <strong>
                                <span style="color:blue;">
                                    <?= number_format(getSumAbonosByMonth($db, $carnets, $arrA['key'], 1, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 2, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 3, True), 2) ?>
                                </span>
                            </strong>
                        </td>
                        <?php
                    }
                    if( $arrTrimestre[$arrA['key']][2] < 3 ) {
                        ?>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][2]) ?>" align="right">Trimestre 2</td>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][2]) ?>" align="left">
                            <strong>
                                <span style="color:blue;">
                                    <?= number_format(getSumAbonosByMonth($db, $carnets, $arrA['key'], 4, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 5, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 6, True), 2) ?>
                                </span>
                            </strong>
                        </td>
                        <?php
                    }
                    if( $arrTrimestre[$arrA['key']][3] < 3 ) {
                        ?>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][3]) ?>" align="right">Trimestre 3</td>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][3]) ?>" align="left">
                            <strong>
                                <span style="color:blue;">
                                    <?= number_format(getSumAbonosByMonth($db, $carnets, $arrA['key'], 7, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 8, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 9, True), 2) ?>
                                </span>
                            </strong>
                        </td>
                        <?php
                    }
                    if( $arrTrimestre[$arrA['key']][4] < 3 ) {
                        ?>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][4]) ?>" align="right">Trimestre 4</td>
                        <td colspan="<?= (3 - $arrTrimestre[$arrA['key']][4]) ?>" align="left">
                            <strong>
                                <span style="color:blue;">
                                    <?= number_format(getSumAbonosByMonth($db, $carnets, $arrA['key'], 10, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 11, True) + getSumAbonosByMonth($db, $carnets, $arrA['key'], 12, True), 2) ?>
                                </span>
                            </strong>
                        </td>
                        <?php
                    }
                }
                ?>
                <td align="right" >&nbsp;</td>
                <td align="right" >&nbsp;</td>
            </tr>
        </tbody>
    </table>
    <?php
    }
    if( isEmpty(getParam("ajax")) ) {
        ?>
            </div>
        </body>
        <?php
    }
    $_SESSION["hide"] = serialize($hide);
    ?>
</html>